﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;

namespace EntityFramework
{
    class Program
    {
        static void Main(string[] args)
        {

            using (NorthWindEntities db = new NorthWindEntities())
            {

                Console.WriteLine("***** Test Join *****");
                {
                    var query =
                           from e in db.Employees
                           from et in e.Territories
                           where e.City == "Seattle"
                           select new { e.FirstName, e.LastName, et.TerritoryDescription };

                    foreach (var r in query)
                    {
                        Console.WriteLine(String.Format(" {0} , {1}",
                            r.FirstName, r.TerritoryDescription));
                    }
                }


                Console.WriteLine("***** Test Average() *****");
                {
                    decimal? avg = db.Orders.Select(o => o.Freight).Average();

                    Console.WriteLine(String.Format("Average Freight: {0} ", avg));

                }

                Console.WriteLine("***** Test Sum() *****");
                {
                    int count = db.Products.Count(p => !p.Discontinued);

                    Console.WriteLine(String.Format("Not Disconinues Product Count: {0} ", count));

                }


                Console.WriteLine("***** Test 1 *****");
                {
                    string entitySQL = "SELECT VALUE o FROM ORDERS AS o WHERE  o.ShipCountry = 'Germany';";
                    ObjectQuery<Orders> query = db.CreateQuery<Orders>(entitySQL);

                    foreach (Orders o in query)
                    {
                        Console.WriteLine(o.ShipPostalCode);
                    }
                }

                Console.WriteLine("***** Test 2 *****");
                {
                    var query = from c in db.Customers
                                where c.City == "London"
                                orderby c.ContactName
                                select c;

                    int cc = query.Count();

                    foreach (Customers c in query)
                    {
                        Console.WriteLine(c.CompanyName);
                    }
                }

                Console.WriteLine("***** Test 3 *****");
                {
                    string scity = "London";
                    Customers c = db.Customers.FirstOrDefault(cd => cd.City == scity);
                    Console.WriteLine(c.CompanyName);
                }

                Console.WriteLine("***** Test 4 *****");
                {
                    DateTime dt = new DateTime(1997, 1, 1);
                    var query = from order in db.Orders
                                where order.OrderDate < dt
                                select order;

                    foreach (Orders o in query)
                    {
                        Console.WriteLine(o.OrderDate.ToString());
                    }
                }

                Console.WriteLine("***** Test GroupBy *****");
                {
                    var query = from order in db.Orders
                                group order by order.Customers into result
                                select new
                                {
                                    CustID = result.Key,
                                    Count = result.Count()
                                };

                    foreach (var r in query)
                    {
                        Console.WriteLine(String.Format("Customer ID: {0} , Order Count : {1}",
                            r.CustID, r.Count));
                    }
                }


                Console.WriteLine("***** Test View *****");
                {
                    var query = from et in db.SeattleEmployeeTerritories
                                select et;
                   

                    foreach (var r in query)
                    {
                        Console.WriteLine(String.Format("Title: {0} ,Territory:  {1}",
                            r.Title, r.TerritoryDescription));
                    }
                }


                Console.WriteLine("***** Test 5 *****");
                {
                    Categories c = new Categories();
                    c.CategoryName = "Test Category";
                    c.Description = "My Description";
                    db.AddToCategories(c);
                    db.SaveChanges();

                    Console.WriteLine(c.CategoryID); //TODO auto-increament test

                    c.Description = "My modified description";
                    db.SaveChanges();

                    db.DeleteObject(c);
                    db.SaveChanges();
                }

                Console.WriteLine("***** Test 6 *****");
                {
                    Customers cust = new Customers();
                    cust.CustomerID = "MTMTM";
                    cust.ContactName = "Irantha";
                    cust.CompanyName = "EffiProz Systems";
                    cust.Country = "Netherlands";
                    cust.City = "Amsterdam";
                    cust.Phone = "012345677";
                    db.AddToCustomers(cust);
                    db.SaveChanges();

                    db.DeleteObject(cust);
                    db.SaveChanges();
                }
            }


            Console.WriteLine("All Tests Successfull. Press Any Key to Continue...");
            Console.ReadKey();
        }

    }
}
